﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Xml;
using System.IO;
using Antu.DataAccess;
namespace Antu.Business
{
    /// <summary>
    /// 政策法规数据交换类
    /// </summary>
   public class ZCFGTranslation
    {
       /// <summary>
       /// 从外网政策法规导出为临时文件
       /// </summary>
       /// <param name="chkText"></param>
       public void GetZcfgFromOutWeb(string chkText)
       {
           string strSql = string.Format("select * from zhengcefagui where can_sync=1");
           DataTable dtTemp;
           SysParams.OAConnection(false,"outweb").RunSql(strSql, out dtTemp);
           DataSet ds = new DataSet();
           ds.Tables.Add(dtTemp);
           if (!Directory.Exists("WorkDir"))
           {
               Directory.CreateDirectory("WorkDir");
           }

           string strFileName = "WorkDir\\" + chkText + "_web.xml";

           //转变编码
           XmlTextWriter xtwTemp = new XmlTextWriter(strFileName, Encoding.Unicode);
           ds.WriteXml(xtwTemp);
           xtwTemp.Close();
           ds.Dispose();
       }

       /// <summary>
       /// 获取列对应的值
       /// </summary>
       /// <param name="drSource">数据行</param>
       /// <param name="strColumnName">列名</param>
       /// <returns></returns>
       public object GetColumnValue(DataRow drSource,string strColumnName)
       {
           if (drSource.Table.Columns.Contains(strColumnName))
           {
               return drSource[strColumnName];
           }
           return string.Empty;
       }

       /// <summary>
       /// 政策法规临时文件导入到内网
       /// </summary>
       /// <param name="chkText"></param>
       public void InputZcfgToInside(string chkText)
       {
           string strSql = "";
           DataSet dsGet = new DataSet();
           dsGet.ReadXml("WorkDir\\" + chkText + "_web.xml");
           DataTable dtTemp = new DataTable();
           if (dsGet.Tables.Count > 0)
           {
               dtTemp = dsGet.Tables[0];
           }
           foreach(DataRow row in dtTemp.Rows)
           {
               IDataParameter[] idParams = DataFactory.GetParameter(DatabaseType.Oracle, 11);
               idParams[0] = new OracleParameter("vid", OracleType.VarChar);
               idParams[0].Value = GetColumnValue(row,"id");
               idParams[1] = new OracleParameter("vjibie", OracleType.VarChar);
               idParams[1].Value = GetColumnValue(row, "jibie");
               idParams[2] = new OracleParameter("vnum", OracleType.VarChar);
               idParams[2].Value = GetColumnValue(row, "num");
               idParams[3] = new OracleParameter("vtitle", OracleType.VarChar);
               idParams[3].Value = GetColumnValue(row, "title");
               idParams[4] = new OracleParameter("vclassname", OracleType.VarChar);
               idParams[4].Value = GetColumnValue(row, "classname");
               idParams[5] = new OracleParameter("vfawendanwei", OracleType.VarChar);
               idParams[5].Value = GetColumnValue(row, "fawendanwei");
               idParams[6] = new OracleParameter("vfawendate", OracleType.DateTime);
               idParams[6].Value = GetColumnValue(row, "fawendate");
               idParams[7] = new OracleParameter("veditor", OracleType.VarChar);
               idParams[7].Value = GetColumnValue(row, "editor");
               idParams[8] = new OracleParameter("vupdatetime", OracleType.DateTime);
               idParams[8].Value = GetColumnValue(row, "updatetime");
               idParams[9] = new OracleParameter("vhits", OracleType.Int32);
               idParams[9].Value = GetColumnValue(row, "hits");
               
               idParams[10] = new OracleParameter("VCONTENT", OracleType.Clob);
               idParams[10].Value = row["CONTENT"].ToString();

               strSql = string.Format("select * from zhengcefagui where id='{0}'", row["id"]);
               DataTable dtZcfg;
               SysParams.OAConnection(false,"inweb").RunSql(strSql, out dtZcfg);
               if (dtZcfg.Rows.Count <= 0)
               {
                   strSql = string.Format(@"insert into zhengcefagui(id,jibie,num,title,classname,fawendanwei,fawendate,editor,
                                            updatetime,hits,CONTENT) 
                                            values(:vid,:vjibie,:vnum,:vtitle,:vclassname,:vfawendanwei,:vfawendate,:veditor,
                                            :vupdatetime,:vhits,:VCONTENT)");
               }
               else
               {
                   strSql = string.Format(@"update zhengcefagui set id=:vid,jibie=:vjibie,num=:vnum,title=:vtitle,classname=:vclassname,
                                            fawendanwei=:vfawendanwei,fawendate=:vfawendate,editor=:veditor,updatetime=:vupdatetime,
                                            hits=:vhits,CONTENT=:VCONTENT where id='{0}'",row["id"]);
               }
               SysParams.OAConnection(false,"inweb").RunSql(strSql, ref idParams);
           }
       }
      /// <summary>
       /// 将需要特殊处理的表导入内网
      /// </summary>
      /// <param name="dsSource"></param>
      /// <param name="tableName"></param>
       public void InputStandTableToInside(DataSet dsSource, string tableName)
       {
           string strSql = "";
           Antu.DataAccess.IDataAccess ida = SysParams.OAConnection(true, "inweb");
           DataTable dtTemp = new DataTable();
           if (dsSource.Tables.Count > 0)
           {
               try
               {
                   DataTable dtColumns = GetDtColumns(tableName);
                   dtTemp = dsSource.Tables[tableName];
                   foreach (DataRow row in dtTemp.Rows)
                   {
                       IDataParameter[] iparams = GetParameters(tableName, row, dtTemp);
                       strSql = string.Format("select * from {1} where id='{0}'", row["id"], tableName);
                       DataTable dtStand;
                       SysParams.OAConnection(false, "inweb").RunSql(strSql, out dtStand);
                       if (dtStand.Rows.Count > 0)
                       {
                           strSql = UpdateSqlCommand(dtColumns, tableName, "id", row["id"].ToString(), dtTemp);
                       }
                       else
                       {
                           strSql = InsertSqlCommand(dtColumns, tableName, dtTemp);
                       }
                       ida.RunSql(strSql, ref iparams);
                   }
                   ida.Close(true);
               }
               catch 
               {
                   ida.Close(false);
                   throw;
               }
           }
       }
       /// <summary>
       /// 获得字段及类型dttable
       /// </summary>
       /// <param name="tableName"></param>
       /// <returns></returns>
       private DataTable GetDtColumns(string tableName)
       {
           string strSql = string.Format("select * from user_tab_columns where table_Name='{0}'", tableName);
           DataTable dtColumns=new DataTable();
           SysParams.OAConnection(false, "inweb").RunSql(strSql, out dtColumns);
           return dtColumns;
       }

       /// <summary>
       /// 自动创建IDataParameters
       /// </summary>
       /// <param name="tableName"></param>
       /// <param name="rowStand"></param>
       /// <param name="dtSource"></param>
       /// <returns></returns>
       public IDataParameter[] GetParameters(string tableName,DataRow rowStand,DataTable dtSource)
       { 
           IDataParameter[] iparams;
           DataTable dtColumns=GetDtColumns(tableName);
          
           int colNum = 0;
           foreach (DataRow row in dtColumns.Rows)
           {
               string strColName = row["column_name"].ToString();
               if (dtSource.Columns.Contains(strColName))
               {
                   colNum++;
               }
           }
           iparams = DataFactory.GetParameter(DatabaseType.Oracle, colNum);
           int i = 0;
           foreach (DataRow row in dtColumns.Rows)
           {
               string strColName = row["column_name"].ToString();
               string strColType = row["data_type"].ToString();

               if (dtSource.Columns.Contains(strColName))
               {
                   OracleType oType = GetColumnType(strColType);
                   iparams[i] = new OracleParameter("V" + strColName, oType);
                   string colValue = "";
                   colValue = rowStand[strColName].ToString();
                   if (oType == OracleType.Number)
                   {
                       if (colValue == "")
                       {
                           iparams[i].Value = DBNull.Value;
                       }
                       else
                       {
                           iparams[i].Value = Convert.ToDecimal(colValue);
                       }
                   }
                   else if (oType == OracleType.DateTime)
                   {
                       if (colValue == "")
                       {
                           iparams[i].Value = DBNull.Value;
                       }
                       else
                       {
                           iparams[i].Value = Convert.ToDateTime(colValue);
                       }
                       
                   }
                   else
                   {
                       iparams[i].Value = colValue;
                   }
                   i++;
               }
           }
           return iparams;
       }

       /// <summary>
       /// 返回字段的oracle字段类型
       /// </summary>
       /// <param name="strFiledType"></param>
       /// <returns></returns>
       private OracleType GetColumnType(string strFiledType)
       {
           OracleType otype=new OracleType();
           strFiledType = strFiledType.ToUpper();
           if (strFiledType.IndexOf("VARCHAR2") >= 0 || strFiledType.IndexOf("CHAR") >= 0)
           {
               otype= OracleType.VarChar;
           }
           else if (strFiledType.IndexOf("CLOB") >= 0)
           {
               otype = OracleType.Clob;
           }
           else if (strFiledType.IndexOf("DATE") >= 0)
           {
               otype = OracleType.DateTime;
           }
           else if (strFiledType.IndexOf("NUMBER") >= 0 || strFiledType.IndexOf("LONG") >= 0
           || strFiledType.IndexOf("FLOAT") >= 0 || strFiledType.IndexOf("INTEGER") >= 0)
           {
               otype = OracleType.Number;
           }
           return otype;
       }
     
       /// <summary>
       /// 获取insert语句
       /// </summary>
       /// <param name="dtColumns"></param>
       /// <param name="tableName"></param>
       /// <param name="dtSource"></param>
       /// <returns></returns>
       private string InsertSqlCommand(DataTable dtColumns,string tableName,DataTable dtSource)
       {
           string strSql = string.Empty;
           
           StringBuilder sbRealColumn = new StringBuilder();
           StringBuilder sbValueColumn = new StringBuilder();

           foreach (DataRow drOne in dtColumns.Rows)
           {
               if (dtSource.Columns.Contains(drOne["Column_name"].ToString()))
               {
                   if (sbRealColumn.Length > 0)
                   {
                       sbRealColumn.Append(",");
                       sbValueColumn.Append(",");
                   }

                   string strColumnName = drOne["Column_name"].ToString();
                   sbRealColumn.Append(strColumnName);

                   string strParam = ":" + "V"+strColumnName;
                   sbValueColumn.Append(strParam);
               }
           }
           strSql = string.Format("insert into {0}({1}) values({2})", tableName, sbRealColumn.ToString(), sbValueColumn.ToString());
           return strSql;
       }
       /// <summary>
       /// 获取Update语句
       /// </summary>
       /// <param name="dtColumns"></param>
       /// <param name="tableName"></param>
       /// <param name="key"></param>
       /// <param name="keyValue"></param>
       /// <param name="dtSource"></param>
       /// <returns></returns>
       private string UpdateSqlCommand(DataTable dtColumns, string tableName, string key, string keyValue,DataTable dtSource)
       {
           string strSql = "";
           StringBuilder sbRealColumn = new StringBuilder();
           foreach (DataRow drOne in dtColumns.Rows)
           {
               if (dtSource.Columns.Contains(drOne["Column_name"].ToString()))
               {
                   if (sbRealColumn.Length > 0)
                   {
                       sbRealColumn.Append(",");
                   }
                   string strColumnName = drOne["Column_name"].ToString();
                   sbRealColumn.Append(strColumnName);
                   sbRealColumn.Append("=");
                   string strParam = ":" + "V" + strColumnName;
                   sbRealColumn.Append(strParam);
               }
           }
           strSql = string.Format("update {0} set {1} where {2}='{3}'",tableName,sbRealColumn.ToString(),key,keyValue);
           return strSql;
       }
   }
}
